﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Utility.Data.DbClient;

namespace Quick.CodeGenerator.Data
{
    public class Oracle : IDataBase
    {
        Utility.DBUtility.DatabaseOperate.DbHelperOracle m_Oracle = new Utility.DBUtility.DatabaseOperate.DbHelperOracle();
        /// <summary>
        /// 表字段名称
        /// </summary>
        public override string TableName { get { return "TNAME"; } }
        /// <summary>
        /// 根据关键字,获取指定的表名
        /// </summary>
        /// <param name="keyWords"></param>
        /// <returns></returns>
        public override List<Model.TableInfo> GetTableListInfo(string keyWords)
        {
            string sql = "SELECT TNAME TABLENAME,TABTYPE TABLETYPE FROM TAB WHERE (TABTYPE='TABLE' OR TABTYPE='VIEW') ";

            string sqlWhere = CreateTableInfoSqlWhere(keyWords);
            if (!string.IsNullOrEmpty(sqlWhere))
            {
                sql += $" AND {sqlWhere} ";
            }
            return m_Oracle.Query<Model.TableInfo>(sql, this.m_ConnectonString);
        }
        /// <summary>
        /// 获取指定表名的列数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override List<Model.ColumnInfo> GetColumnInfoList(string tableName)
        {
            string sql = @" SELECT 
                                    USER_TAB_COLUMNS.COLUMN_NAME,
                                    USER_TAB_COLUMNS.DATA_TYPE,
                                    USER_TAB_COLUMNS.DATA_LENGTH,
                                    USER_TAB_COLUMNS.DATA_PRECISION,
                                    USER_TAB_COLUMNS.DATA_SCALE,
                                    USER_TAB_COLUMNS.NULLABLE,
                                    USER_TAB_COLUMNS.DATA_DEFAULT,
                                    USER_COL_COMMENTS.COMMENTS,
                                    USER_TAB_COLUMNS.TABLE_NAME,
                                    CU.CONSTRAINT_TYPE,
                                    USER_OBJECTS.OBJECT_TYPE
                            FROM 
                                    USER_TAB_COLUMNS 
                                    INNER JOIN USER_COL_COMMENTS
                                    ON    USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAME
                                            AND USER_TAB_COLUMNS.TABLE_NAME = USER_COL_COMMENTS.TABLE_NAME
                                            AND USER_TAB_COLUMNS.TABLE_NAME = :TABLE_NAME
                                    INNER JOIN USER_OBJECTS
                                    ON    USER_TAB_COLUMNS.TABLE_NAME = USER_OBJECTS.OBJECT_NAME AND USER_OBJECTS.OBJECT_TYPE IN('TABLE','VIEW')
                                    LEFT JOIN 
                                    (
                                        SELECT 
                                                CU.COLUMN_NAME,
                                                AU.CONSTRAINT_TYPE
                                        FROM 
                                                USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
                                        WHERE 
                                                CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME 
                                                AND AU.CONSTRAINT_TYPE = 'P' 
                                                AND AU.TABLE_NAME =  :TABLE_NAME
                                    ) CU
                                    ON
                                    USER_TAB_COLUMNS.COLUMN_NAME = CU.COLUMN_NAME
                                ORDER BY CU.CONSTRAINT_TYPE,USER_TAB_COLUMNS.COLUMN_ID ";
            DBParameter[] parameters = { new DBParameter(":TABLE_NAME", DBType.Oracle_Varchar2, 50) };
            parameters[0].Value = tableName;
            return TransfromDbType2DbParameterType(m_Oracle.Query<Model.ColumnInfo>(sql, this.m_ConnectonString, parameters));
        }
        /// <summary>
        /// 根据查询的数据类型转换成OracleDbType数据类型
        /// </summary>
        /// <param name="type">数据类型</param>
        /// <param name="isDecimal">是否是小数</param>
        /// <param name="length">数据长度</param>
        /// <returns></returns>
        public override string TransfromDbType2DbParameterType(string type, string decimalStr, string length)
        {
            bool isDecimal = !string.IsNullOrEmpty(decimalStr) && int.Parse(decimalStr) > 0;
            if (string.IsNullOrEmpty(type))
            {
                return string.Empty;
            }

            if (string.Equals(type, "INTEGER", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && !isDecimal))
            {

                return "DBType.Oracle_Long, Length = " + length;
            }
            else if (string.Equals(type, "DECIMAL", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && isDecimal))
            {
                return "DBType.Oracle_Decimal, Length = " + length;
            }
            else if (string.Equals(type, "date", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Date";
            }
            else if (string.Equals(type, "nvarchar2", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_NVarchar2, Length = " + length;
            }
            else if (string.Equals(type, "varchar2", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Varchar2, Length = " + length;
            }
            else if (string.Equals(type, "char", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Char, Length = " + length;
            }
            else if (string.Equals(type, "varchar", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Varchar, Length = " + length;
            }
            else if (string.Equals(type, "clob", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Clob, Length = " + length;
            }
            else if (string.Equals(type, "nclob", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_NClob, Length = " + length;
            }
            return "DBType.Oracle_NVarchar2, Length = " + length;
        }
        /// <summary>
        /// 根据查询的数据类型转换成C#数据类型
        /// </summary>
        /// <param name="type">数据类型</param>
		/// <param name="decimalStr">是否是小数</param>
        /// <param name="length">length</param>
		/// <param name="canNullStr">能否为空</param>
        /// <returns></returns>
        public override string TransfromDbType2CCsharp(string type, string decimalStr, string length, string canNullStr)
        {
            bool isDecimal = !string.IsNullOrEmpty(decimalStr) && int.Parse(decimalStr) > 0;
            bool canNull = "Y".Equals(canNullStr, StringComparison.OrdinalIgnoreCase);
            if (string.IsNullOrEmpty(type))
            {
                return string.Empty;
            }

            if (string.Equals(type, "INTEGER", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && !isDecimal))
            {

                return canNull ? "int?" : "int";
            }
            else if (string.Equals(type, "DECIMAL", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && isDecimal))
            {
                return canNull ? "decimal?" : "decimal";
            }
            else if (string.Equals(type, "date", StringComparison.OrdinalIgnoreCase))
            {
                return canNull ? "DateTime?" : "DateTime";
            }
            else if (string.Equals(type, "nvarchar2", StringComparison.OrdinalIgnoreCase)
                  || string.Equals(type, "varchar2", StringComparison.OrdinalIgnoreCase)
                  || string.Equals(type, "char", StringComparison.OrdinalIgnoreCase)
                  || string.Equals(type, "varchar", StringComparison.OrdinalIgnoreCase))
            {
                return "string";
            }

            return "string";
        }
    }
}
